Amazon Redshift：列の別名参照値が同一クエリ内で宣言後すぐに使えるようになりました

小ネタです。

先日の公式アナウンスにて、Amazon Redshiftが「lateral column alias reference」(横列の別名参照)をサポートするようになりました。当エントリではその内容について紹介してみたいと思います。

• Amazon Redshift announces support for lateral column alias reference
• SELECT List - Amazon Redshift

Amazon Redshift now enables you to write queries that refer to a column alias within the same query immediately after it is declared, improving the readability of complex SQL queries. https://t.co/JO7AwznlfF pic.twitter.com/BIsJ9D3MoM

— Amazon Web Services (@awscloud) 2018年8月8日

「lateral column alias reference」(横列の別名参照)とは

公式エントリで紹介されているクエリを例に見てみましょう。今回紹介する「lateral column alias reference」がサポートされた事によって、今後は列の別名として定義していた値(SQLの列で言うとprobabilityの部分))が同一クエリ内で直後から利用出来るようになりました。

select
  clicks / impressions as probability,   /** ここで定義したprobabilityという値を */
  round(100 * probability, 1) as percentage /** 同一クエリ内のすぐ後で参照・利用出来る */
from
  raw_data;

以前までは、以下の様な形で定義しないとエラーとなってしまいました。今回のこの機能がサポートされる事によりSQLを記述する量を減らす事が出来、また可読性も上がる事が期待出来ますね。

select
  rd.probability,
  round(100 * rd.probability, 1) as percentage
from
  (select
    clicks / impressions as probability
  from
    raw_data) rd;

実データを使って検証してみました。下記のような形であれば、1回のクエリで欲しい値が取ってこれています。

# SELECT
  order_id,
  sales,
  profit,
  profit / sales as profit_rate,
  ROUND(profit_rate, 2) * 100 as profit_percentage
FROM
  public.superstore_orders
LIMIT 30;
    order_id     |  sales  |  profit  |     profit_rate     | profit_percentage 
-----------------+---------+----------+---------------------+-------------------
 JP-2012-2452002 |   34584 |     7262 |   0.209981494332639 |                21
 JP-2012-2452002 |    1168 |      384 |   0.328767123287671 |                33
 JP-2012-2452002 |    7992 |     1422 |   0.177927927927928 |                18
 JP-2012-1338137 |    2260 |      268 |    0.11858407079646 |                12
 JP-2012-1553749 |    1470 |     -378 |  -0.257142857142857 |               -26
 JP-2012-1036650 |   14378 |     7182 |   0.499513145082765 |                50
 JP-2012-3620423 | 31089.6 | -13990.4 |  -0.450002573207761 |               -45
 JP-2012-1082729 |   14340 |     6730 |    0.46931659693166 |                47
 JP-2012-1095807 |    9792 |     1758 |    0.17953431372549 |                18
 JP-2012-1222461 |    2814 |       84 |  0.0298507462686567 |                 3
 JP-2012-1222461 |    7920 |     3800 |    0.47979797979798 |                48
 JP-2012-1754955 |  1987.2 |   -764.8 |  -0.384863123993559 |               -38
 JP-2012-1505056 |   10842 |      255 |  0.0235196458218041 |                 2
 JP-2012-1505056 |   11604 |     3942 |    0.33971044467425 |                34
 JP-2012-2766769 |    1680 |      756 |                0.45 |                45
 JP-2012-1292893 |  1422.9 |    -17.1 | -0.0120177103099304 |                -1
 JP-2012-1516848 | 37574.4 |  -6889.6 |  -0.183358882643502 |               -18
 JP-2012-1516848 | 20395.2 |  -8164.8 |  -0.400329489291598 |               -40
 JP-2012-2030706 |   28416 |    11640 |   0.409628378378378 |                41
 JP-2012-1609809 |    1032 |      144 |    0.13953488372093 |                14
 JP-2012-2435780 |    4542 |       85 |  0.0187142228093351 |                 2
 JP-2012-1055699 |   13128 |     1968 |   0.149908592321755 |                15
 JP-2012-1055699 |   91980 |     6426 |  0.0698630136986301 |                 7
 JP-2012-2571817 |  104030 |    26000 |     0.2499279054119 |                25
 JP-2012-2081497 |    9300 |       85 | 0.00913978494623656 |                 1
 JP-2012-1385890 |  2236.8 |   -939.2 |  -0.419885550786838 |               -42
 JP-2012-1385890 |   236.4 |   -119.6 |  -0.505922165820643 |               -51
 JP-2012-2025114 |    9402 |     4416 |   0.469687300574346 |                47
 JP-2012-1390970 |    2200 |      132 |                0.06 |                 6
 JP-2012-1390970 |    3152 |      346 |   0.109771573604061 |                11
(30 rows)

ちなみにカラムの順序を入れ替えてみた(定義が為される前に列別名を参照する)場合、エラーとなりました。

# SELECT
  order_id,
  sales,
  profit,
  ROUND(profit_rate, 2) * 100 as profit_rate_percentage,
  profit / sales as profit_rate
FROM
  public.superstore_orders
LIMIT 30;

ERROR:  column "profit_rate" does not exist in superstore_orders

まとめ

という訳でAmazon Redshiftの列別名に関するちょっと便利な機能改善のお知らせでした。コードの記述量削減に大いに期待が持てる改善ですので、今後は積極的に使っていきたいところです。